<?
	session_start();
	error_reporting(E_ALL);
	include('conn.php');

	//$qry1 = "SELECT CustId,socId,payamount,sum(payamount) as paid from payments group by CustId order by payid desc";
	$qry1 = "select tt1.CustId, 
		case when sum(payamount) is null then totalbillamount else  totalbillamount - sum(payamount) end as outstanding,
		totalbillamount from (select tt.CustId, sum(cost) as totalbillamount from 
		(SELECT CustId, case when terminationdate is not null then (DATEDIFF(terminationdate,activationdate)/30)*ratepermonth 
		else (DATEDIFF(CURDATE(),activationdate)/30)*ratepermonth end as cost FROM 
		cableappdev.STBtoCustomer) tt  group by tt.CustId ) tt1 left join payments p on tt1.CustId = p.CustId 
		group by p.CustId ";
	$res1 = mysql_query($qry1);
	$data = array();
	$i = 0;

	 while($rows = mysql_fetch_array($res1))
	{
		/*$qry2 = "SELECT c.CustomerName,c.RoomNumber,s.societyname,sum(sc.RateperMonth) as totalrpm, 
		sc.`TerminationDate`,sc.`ActivationDate`, sum(((period_diff(date_format((case when isnull(sc.`TerminationDate`) then
		now() when ( sc.`TerminationDate` = '0000-00-00 00:00:00') then now()  else  sc.`TerminationDate` end),'%Y%m'),		date_format(sc.`ActivationDate`,'%Y%m')) + 1) *  sc.`RateperMonth`)) AS `totalout` from Customer c, society s, STBtoCustomer sc where c.CustId = '".$rows['CustId']."' and s.socid = '".$rows['socId']."' and sc.CustId='".$rows['CustId']."';";			*/
		
		$qry2="select c.CustomerName,c.RoomNumber,s.societyname from society s , Customer c
		where c.custId='".$rows['CustId']."' and c.socId=s.socId;";
		//echo "<br>$qry2<br>".var_dump($rows)."<br>";
		
		$res2 = mysql_query($qry2);
		$row2 = mysql_fetch_array($res2);
				
		$data[$i] = array
			(									
				"sname" => $row2['societyname'],			
				"room" => $row2['RoomNumber'],			
				"cid" => $rows['CustId'],			
				"cname" => $row2['CustomerName'],														
				//"payamt" => $rows['payamount'],
				//"paid" => $rows['paid'],
				//"totalrpm" => $rows['totalbillamount'],				
				//"totalout" => $rows['outstanding'],
				"currout" => $rows['outstanding'] 
			);		
		$i++;		
	}	
	echo json_encode($data);
?>